package testData;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Random;
import java.util.UUID;

import utils.DBUtils;

public class UserDao {

	private static Connection conn = DBUtils.getConnection();

	

	/**
	 * @param testTableNum  测试表数量
	 * @param data			测试表数据量
	 * @param deleteNum		删除数量
	 * @return
	 */
	public static long deleteData(int testTableNum, int data, int deleteNum) {
		long begin = System.currentTimeMillis();
		long end = 0;
		long diffTime = 0;
		PreparedStatement ps = null;
		// Statement statement = null;
		ResultSet rs = null;
		try {
			for (int i = 0; i < testTableNum; i++) {
				String sql = "delete from demo" + i + " where id between " + data + " And " + (data + deleteNum - 1);
				ps = conn.prepareStatement(sql);
				ps.execute(sql);
				conn.commit();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		end = System.currentTimeMillis();
		diffTime = (end - begin) / testTableNum;
		return diffTime;
	}

	/**
	 * @param testTableNum
	 *            测试表数量
	 * @param data
	 *            测试表数据量
	 * @param addNum
	 *            插入数量
	 * @param indexNum
	 *            索引值
	 * @return
	 */
	public static long insert(int testTableNum, int data, int addNum) {
		long begin = System.currentTimeMillis();
		long end = 0;
		long diffTime = 0;
		PreparedStatement ps = null;

		Random r = new Random();
		try {
			conn.setAutoCommit(false);
			for (int i = 0; i < testTableNum; i++) {
				String sql = "insert into demo" + i + " values(?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(sql);
				int id = data;
				for (int j = 0; j < addNum; j++) {
					ps.setInt(1, id++);
					ps.setInt(2, j);
					ps.setString(3, UUID.randomUUID().toString());
					ps.setInt(4, r.nextInt(10000));
					ps.setInt(5, r.nextInt(10000));
					ps.setInt(6, r.nextInt(10000));
					ps.setInt(7, j);
					ps.addBatch();
				}
				ps.executeBatch();
			}
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		}
		end = System.currentTimeMillis();
		diffTime = (end - begin) / testTableNum;
		System.err.println("--一次--" + data + "条数据----插入" + addNum + "条数据，的评价时间是------:" + diffTime + " ms");
		return diffTime;
	}

	/**
	 * @param testTableNum
	 *            测试表数量
	 * @param index
	 *            索引条数
	 * @param data
	 *            数据量
	 * @param type
	 * @return
	 */
	public static long selectByIndex(int testTableNum, int index, int data, int type) {
		long begin = System.nanoTime();
		long end = 0;
		long diffTime = 0;
		PreparedStatement ps = null;
		// Statement statement = null;
		ResultSet rs = null;
		try {
			for (int i = 0; i < testTableNum; i++) {
				String sql = "select * from demo" + i + " where type = 3 ";
				// String sql1 = "select * from " + tableName + "where id in(2,1)";
				// String sql2 = "select * from " + tableName + "where id=2 or
				// id=3";
				ps = conn.prepareStatement(sql);
				// 2.获取Statement
				// statement = conn.createStatement();
				// 3.执行查询，得到ResultSet
				rs = ps.executeQuery(sql);
//				while(rs.next()){
//					System.err.println(rs.getInt("id"));
//				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		end = System.nanoTime();
		diffTime = (end - begin) / testTableNum;
		System.err.println("--一次--" + data + "条数据----查询" + type + "条数据，的评价时间是------:" + diffTime + " ns");
		return diffTime;
	}

	/**
	 * @param tableName
	 *            表名
	 * @param data
	 *            数据库数量
	 * @param type
	 */
	public static void addData(String tableName, int data, int type) {
		long begin = System.currentTimeMillis();
		long end = 0;
		PreparedStatement ps = null;
		int id = 0;
		int z = data / type;
		try {
			conn.setAutoCommit(false);
			String sql = "insert into " + tableName + "(type,score,id,name,age,height,weight) value(?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			// System.err.println(sql);
			Random r = new Random();
			// 百万级以上 批量提交10w条数据
			if (data >= 1000000) {
				for (int i = 1; i <= data / 100000; i++) {
					for (int k = 1; k <= 100000; k++) {
						ps.setInt(1, id % z);
						ps.setInt(2, id % z);
						ps.setInt(3, id++);
						ps.setString(4, UUID.randomUUID().toString());
						ps.setInt(5, r.nextInt(100000));
						ps.setInt(6, r.nextInt(100000));
						ps.setInt(7, r.nextInt(100000));
						ps.addBatch();
					}
					ps.executeBatch();
					conn.commit();
				}
			} else {
				for (int i = 1; i <= data / 10000; i++) {
					for (int k = 1; k <= 10000; k++) {
						ps.setInt(1, id % z);
						ps.setInt(2, id % z);
						ps.setInt(3, id++);
						ps.setString(4, UUID.randomUUID().toString());
						ps.setInt(5, r.nextInt(100000));
						ps.setInt(6, r.nextInt(100000));
						ps.setInt(7, r.nextInt(100000));
						ps.addBatch();
					}
					ps.executeBatch();
					conn.commit();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			end = System.currentTimeMillis();
			System.out.println("向" + tableName + "表添加" + data + "条数据，用时 : " + (end - begin) + " ms");
		}
	}

	/**
	 * @param tableName
	 *            表名
	 */
	public static void deleteTable(String tableName) {
		long begin = System.currentTimeMillis();
		long end = 0;
		try {
			String sql = "DROP table if exists " + tableName;
			conn.createStatement().execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		end = System.currentTimeMillis();
		long diffTime = end - begin;

	}

	/**
	 * MyISAM
	 * 
	 * @param tableName
	 *            表名
	 */
	public static void addTableMyISAM(String tableName) {
		long begin = System.currentTimeMillis();
		long end = 0;
		try {
			String sql = "create table " + tableName + "(" + "`id` int(11) NOT NUll," + "`type` int(11) DEFAULT NULL,"
					+ "`name` varchar(255) DEFAULT NULL," + "`age` int(11) DEFAULT NULL,"
					+ "`height` int(11) DEFAULT NULL," + "`weight` int(11) DEFAULT NULL,"
					+ "`score` int(11) DEFAULT NULL," + "primary key (id),"
					+ "key `type`(type) using btree,key `score`(score) using btree)"
					+ "engine=myIsam default charset = utf8 collate = utf8_bin;";
			conn.createStatement().execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			end = System.currentTimeMillis();
		}
		long diffTime = end - begin;
	}

	/**
	 * InnoDB
	 * 
	 * @param tableName
	 *            表名
	 */
	public static void addTableInnoDB(String tableName) {
		long begin = System.currentTimeMillis();
		long end = 0;
		try {
			String sql = "create table " + tableName + "(" + "`id` int(11) NOT NUll," + "`type` int(11) DEFAULT NULL,"
					+ "`name` varchar(255) DEFAULT NULL," + "`age` int(11) DEFAULT NULL,"
					+ "`height` int(11) DEFAULT NULL," + "`weight` int(11) DEFAULT NULL,"
					+ "`score` int(11) DEFAULT NULL," + "primary key (id),"
					+ "key `type`(type) using btree,key `score`(score) using btree)"
					+ "engine=InnoDB default charset = utf8 collate = utf8_bin;";
			conn.createStatement().execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			end = System.currentTimeMillis();
		}
		long diffTime = end - begin;
	}
	
	
}
